package com.example.mybatisplus.mapper;

import com.example.mybatisplus.model.domain.Violations;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.mybatisplus.model.dto.VioNumberDTO;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.lang.invoke.VolatileCallSite;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author lxp
 * @since 2024-07-17
 */
@Repository
public interface ViolationsMapper extends BaseMapper<Violations> {

    @Insert("INSERT INTO Violations (vio_id, violation_type, violation_category, student_id, name, college, major, grade, result, happen_time, enter_time, effective_time, punishment_duration, unremind_time)\n" +
            "VALUES (#{vioId},#{violationType},#{violationCategory}, #{studentId},#{name},#{college},#{major} ,#{grade},#{result},#{happenTime},#{enterTime},#{effectiveTime},#{punishmentDuration},#{unremindTime})")
    void insertAll(Violations violations);


    @Update("UPDATE Violations SET is_enabled = 0 WHERE vio_id = #{vioId}")
    void deleteById(String vioId);

    @Select("SELECT * FROM Violations WHERE violations.is_enabled = 0")
    List<Violations> getAllRecordNot();

    @Select("SELECT COUNT(*) FROM Violations WHERE result = '警告' AND is_enabled = 1")
    int getResult1Count();

    @Select("SELECT COUNT(*) FROM Violations WHERE result = '严重警告' AND is_enabled = 1")
    int getResult2Count();

    @Select("SELECT COUNT(*) FROM Violations WHERE result = '记过' AND is_enabled = 1")
    int getResult3Count();

    @Select("SELECT COUNT(*) FROM Violations WHERE result = '留校察看' AND is_enabled = 1")
    int getResult4Count();

    @Select("SELECT COUNT(*) FROM Violations WHERE result = '开除学籍' AND is_enabled = 1")
    int getResult5Count();

    @Select("SELECT college AS name, COUNT(*) AS value FROM Violations WHERE is_enabled = 1 GROUP BY college")
    @Results({
            @Result(property = "name", column = "name"),
            @Result(property = "value", column = "value")
    })
    List<VioNumberDTO.CollegeData> getCollegeData();

    @Select("SELECT COUNT(*) FROM Violations WHERE enter_time BETWEEN #{startTime} AND #{endTime} AND result = '警告' AND is_enabled = 1")
    int getViolationsCountByTimeRange1(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    @Select("SELECT COUNT(*) FROM Violations WHERE enter_time BETWEEN #{startTime} AND #{endTime} AND result = '严重警告' AND is_enabled = 1")
    int getViolationsCountByTimeRange2(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    @Select("SELECT COUNT(*) FROM Violations WHERE enter_time BETWEEN #{startTime} AND #{endTime} AND result = '记过' AND is_enabled = 1")
    int getViolationsCountByTimeRange3(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    @Select("SELECT COUNT(*) FROM Violations WHERE enter_time BETWEEN #{startTime} AND #{endTime} AND result = '留校察看' AND is_enabled = 1")
    int getViolationsCountByTimeRange4(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    @Select("SELECT DISTINCT major FROM violations")
    public List<String> getMajor();

    @Select("SELECT DISTINCT violation_type FROM violations")
    public List<String> getKind();

    @Select("SELECT DISTINCT violation_category FROM violations")
    public List<String> getCategory();

    @Select("SELECT COUNT(*) FROM Violations WHERE violation_category = #{violationCategory}")
    int getCategoryCount(String violationCategory);

    @Select("SELECT DISTINCT college FROM violations")
    public List<String> getAcade();

    @Select("SELECT COUNT(*) FROM Violations WHERE enter_time BETWEEN #{startTime} AND #{endTime} AND result = '开除学籍' AND is_enabled = 1")
    int getViolationsCountByTimeRange5(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    @Select("SELECT college AS name, COUNT(*) AS value FROM Violations WHERE enter_time BETWEEN #{startTime} AND #{endTime} AND is_enabled = 1 GROUP BY college")
    @Results({
            @Result(property = "name", column = "name"),
            @Result(property = "value", column = "value")
    })
    List<VioNumberDTO.CollegeData> getCollegeDataByTimeRange(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    default List<String> getNamesByVioIds(List<String> vioIds) {
        List<String> names = new ArrayList<>();
        for (String vioId : vioIds) {
            String name = getNameByVioId(vioId);
            names.add(name);
        }
        return names;
    }
    @Select("SELECT name FROM violations WHERE vio_id = #{vioId}")
    String getNameByVioId(String vioId);

    default List<String> getStudentIdsByVioIds(List<String> vioIds) {
        List<String> studentIds = new ArrayList<>();
        for (String vioId : vioIds) {
            String studentId = getStudentIdByVioId(vioId);
            studentIds.add(studentId);
        }
        return studentIds;
    }
    @Select("SELECT student_id FROM violations WHERE vio_id = #{vioId}")
    String getStudentIdByVioId(String vioId);

    @Update("UPDATE Violations SET punishment_duration = punishment_duration + #{addPunishmentDuration}, " +
            "unremind_time = DATE_ADD(unremind_time, INTERVAL #{addPunishmentDuration} DAY) " +
            "WHERE vio_id = #{vioId}")
    void extendViolation(String vioId, int addPunishmentDuration);

}
